/* 
 * Copyright 2014-2021 UME Framework, GNU General Public License 
 */
package com.u2framework.lib.tool.gen.entity.parser;

import java.io.File;
import java.io.FileFilter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.u2framework.lib.core.util.BeanUtil;
import com.u2framework.lib.core.util.StringUtil;
import com.u2framework.lib.tool.gen.base.type.TypeMapper;
import com.u2framework.lib.tool.gen.entity.EntityConfigurator;
import com.u2framework.lib.tool.gen.entity.EntityGenerator;
import com.u2framework.lib.tool.gen.entity.bean.BaseEntityBean;
import com.u2framework.lib.tool.gen.entity.bean.BaseFieldBean;
import com.u2framework.lib.tool.gen.entity.bean.FieldDescBean;
import com.u2framework.lib.tool.poi.ExcelAccessor;

/**
 * EntityExcelParser
 *
 * @author SultansOfSwing & MaYue
 */
public class EntityExcelParser extends ExcelAccessor {
    /**
     * Identify keys define for Chinese (default) document format
     */
    private String[] excelTargetColumnLabel = new String[] { "序号", "字段名", "字段ID", "数据类型", "长度", "主键", "非空", "默认值", "格式", "最小值", "最大值", "附加定义", "乐观验证", "抽取类型", "备注" };
    /**
     * Ignore sheet name define
     */
    private Set<String> ignoreSheetSet;
    /**
     * EntityGenerator instance
     */
    private EntityGenerator entityGenerator;
    /**
     * EntityConfigurator instance
     */
    private EntityConfigurator entityConfigurator;

    /**
     * EntityExcelParser
     */
    public EntityExcelParser(EntityConfigurator entityConfigurator) throws IOException {
        this.entityConfigurator = entityConfigurator;
        this.entityGenerator = new EntityGenerator(entityConfigurator);
        this.ignoreSheetSet = entityConfigurator.getIgnoreSheetSet();
    }

    /**
     * execute
     * 
     * @param inputPath
     */
    public void execute(String inputPath) {
        try {
            File[] pathnames = new File(inputPath).listFiles(new FileFilter() {
                @Override
                public boolean accept(File e) {
                    return e.isDirectory();
                }
            });
            int i = 1;
            for (File pathname : pathnames) {
                File[] defines = pathname.listFiles(new FileFilter() {
                    @Override
                    public boolean accept(File e) {
                        String name = e.getName().toLowerCase();
                        return name.endsWith(".xls") || name.endsWith(".xlsx") || name.endsWith(".xlsm");
                    }
                });
                Map<String, BaseEntityBean> infoMap = this.parseFile(defines);
                Collection<BaseEntityBean> infoList = infoMap.values();

                String packageName = pathname.getName().toLowerCase();
                entityGenerator.getEntityConfigurator().setBasePackage(packageName);
                entityGenerator.execute("" + (i++), infoList);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * parseFileList
     * 
     * @param files
     * @return
     * @throws Exception
     */
    protected Map<String, BaseEntityBean> parseFileList(List<File> files) throws Exception {
        return parseFile(files.toArray(new File[files.size()]));
    }

    /**
     * parseFile
     * 
     * @param files
     * @return
     * @throws Exception
     */
    protected Map<String, BaseEntityBean> parseFile(File... files) throws Exception {
        Map<String, BaseEntityBean> result = new LinkedHashMap<String, BaseEntityBean>();
        for (File file : files) {
            Map<String, BaseEntityBean> beans = this.parseWorkbook(file);
            for (Map.Entry<String, BaseEntityBean> e : beans.entrySet()) {
                if (result.containsKey(e.getKey())) {
                    throw new Exception("Duplicated Table ID " + e.getValue().getTblId() + " define in " + e.getValue().getDocName() + " and " + result.get(e.getKey()).getDocName());
                }
                result.put(e.getKey(), e.getValue());
            }
        }
        return result;
    }

    /**
     * parseWorkBook
     *
     * @param excelFile
     * @return
     * @throws Exception
     */
    protected Map<String, BaseEntityBean> parseWorkbook(File excelFile) throws Exception {
        Map<String, BaseEntityBean> result = new LinkedHashMap<String, BaseEntityBean>();

        String fileName = excelFile.getName();
        Workbook book = loadExcel(excelFile);
        // Set<String> nameSet = new HashSet<String>();
        Map<String, String> nameSet = new HashMap<String, String>();

        for (int i = 0; i < book.getNumberOfSheets(); i++) {
            String sheetName = book.getSheetName(i);
            if (sheetName.startsWith("#") || (ignoreSheetSet != null && ignoreSheetSet.contains(sheetName.toUpperCase()))) {
                continue;
            }
            // Is target sheet
            Sheet sheet = book.getSheetAt(i);
            BaseEntityBean bean = parseWorkSheet(excelFile, sheetName, sheet);
            String docName = fileName + ":" + sheetName;
            bean.setDocName(docName);

            String key = fileName + ":" + bean.getTblId();
            if (nameSet.containsKey(bean.getTblId())) {
                throw new Exception("Duplicated Table ID " + bean.getTblId() + " define in " + docName + " and " + nameSet.get(bean.getTblId()));
            }
            nameSet.put(bean.getTblId(), docName);
            result.put(key, bean);
        }
        return result;
    }

    /**
     * parseWorkSheet
     *
     * @param excelFile
     * @param sheetName
     * @param sheet
     * @return
     * @throws Exception
     */
    protected BaseEntityBean parseWorkSheet(File excelFile, String sheetName, Sheet sheet) throws Exception {
        TypeMapper typeMapper = this.getEntityGenerator().getEntityConfigurator().getTypeMapper();
        BaseEntityBean bean = new BaseEntityBean();
        String tblName = getCellValueAsString(getCell(sheet, 0, 2));
        String tblId = getCellValueAsString(getCell(sheet, 1, 2));
        String tblDivision = getCellValueAsString(getCell(sheet, 2, 2));
        tblDivision = StringUtil.isNotEmpty(tblDivision) ? "${theDivision}" : "";

        String databaseType = getCellValueAsString(getCell(sheet, 0, 8));
        if (StringUtil.isEmpty(databaseType)) {
            databaseType = this.getEntityGenerator().getEntityConfigurator().getDatabaseType();
        }
        String tblSpace = getCellValueAsString(getCell(sheet, 1, 8));
        String createDefaultColumns = getCellValueAsString(getCell(sheet, 2, 8));

        bean.setTblName(tblName);
        bean.setTblId(tblId);
        bean.setTblAlias(sheetName.trim());
        bean.setTblDivision(tblDivision);
        bean.setDatabaseType(databaseType);
        bean.setTblSpace(tblSpace);
        bean.setCreateDefaultColumns(StringUtil.isNotEmpty(createDefaultColumns) ? true : false );

        String excelTargetDataFlag = excelTargetColumnLabel[0];
        int startRowNum = -1;
        for (int rowIdx = 0; rowIdx < 25; rowIdx++) {
            String cellValue = getCellValueAsString(getCell(sheet, rowIdx, 0));
            if (excelTargetDataFlag.equals(cellValue.trim())) {
                startRowNum = rowIdx + 1;
                break;
            }
        }
        if (startRowNum == -1) {
            throw new Exception("*** No Found [" + excelTargetDataFlag + "] in sheet:" + sheetName);
        }

        int endRowNum = -1;
        for (int rowIdx = startRowNum; rowIdx < startRowNum + 2048; rowIdx++) {
            String cellValue = getCellValueAsString(getCell(sheet, rowIdx, 0));
            if (StringUtil.isEmpty(cellValue) || cellValue.trim().startsWith("[NULL]")) {
                endRowNum = rowIdx - 1;
                break;
            }
        }
        if (endRowNum == -1) {
            throw new Exception("*** No Found row EOF in sheet:" + sheetName);
        }

        int endColNum = -1;
        for (int colIdx = 0; colIdx < 2048; colIdx++) {
            String cellValue = getCellValueAsString(getCell(sheet, startRowNum - 1, colIdx));
            if (StringUtil.isEmpty(cellValue) || cellValue.trim().startsWith("[NULL]")) {
                endColNum = colIdx;
                break;
            }
        }
        if (endColNum == -1) {
            throw new Exception("*** No Found column EOF in sheet:" + sheetName);
        }
        int[] colPositions = new int[endColNum];
        for (int i = 0; i < endColNum; i++) {
            colPositions[i] = i;
        }

        List<List<Object>> actTitlesList = readSheetAsObjectList(excelFile, sheetName, colPositions, startRowNum - 1, startRowNum - 1);
        List<Object> actTitles = actTitlesList.get(0);
        int[] targetColumnPostions = new int[excelTargetColumnLabel.length];
        for (int i = 0; i < targetColumnPostions.length; i++) {
            targetColumnPostions[i] = -1;
        }

        // match title accord's position index value
        for (int i = 0; i < excelTargetColumnLabel.length; i++) {
            String expValue = excelTargetColumnLabel[i];
            for (int j = 0; j < actTitles.size(); j++) {
                String actValue = String.valueOf(actTitles.get(j)).trim();
                if (actValue.equals(expValue)) {
                    targetColumnPostions[i] = j;
                }
            }
        }
        for (int i = 0; i < targetColumnPostions.length; i++) {
            if (targetColumnPostions[i] == -1) {
                throw new Exception("Warning: Title data mismatch. No found except title [" + excelTargetColumnLabel[i] + "] in sheet: " + sheetName + "," + excelFile);
            }
        }

        List<List<Object>> rows = readSheetAsObjectList(excelFile, sheetName, targetColumnPostions, startRowNum, endRowNum);
        Set<String> idSet = new HashSet<>();
        // Process for table/Dto field level elements
        for (List<Object> row : rows) {
            BaseFieldBean field = new BaseFieldBean();
            String itemIndex = getTrimValue(row.get(0));
            String itemName = getTrimValue(row.get(1));
            if (itemName.contains("\n")) {
                itemName = itemName.replaceAll("\n", " ");
            }
            String itemId = getTrimValue(row.get(2));
            String bizType = getTrimValue(row.get(3));
            String length = getTrimValue(row.get(4));
            if (length.startsWith("(") && length.endsWith(")")) {
                length = length.substring(1, length.length() - 1);
            }
            if (length.contains(".")) {
                String[] tmp = length.split("\\.");
                if (Integer.valueOf(tmp[1]).equals(0)) {
                    length = tmp[0];
                }
            }
            String primaryKey = getTrimValue(row.get(5));
            String notNull = getTrimValue(row.get(6));
            String format = getTrimValue(row.get(8));
            String defaultValue = getTrimValue(row.get(7));
            String minValue = getTrimValue(row.get(9));
            String maxValue = getTrimValue(row.get(10));
            String additionalDefine = getTrimValue(row.get(11));
            String optimisticCheck = getTrimValue(row.get(12));

            // 自定义的物理数据类型（带有长度声明格式的定义）
            String colDbTypeDDL = getTrimValue(row.get(13));
            String comment = getTrimValue(row.get(14));
            char firstCharOfColId = itemId.toUpperCase().charAt(0);
            if (!(firstCharOfColId >= 'A' && firstCharOfColId <= 'Z')) {
                continue;
            }

            field.setColNo(itemIndex);
            field.setColId(itemId);
            field.setColName(itemName);

            if (!typeMapper.checkDataType(bizType, colDbTypeDDL, databaseType)) {
                throw new Exception("Unsupport Data Type define in cell[" + itemId + " , " + bizType + "] of sheet[" + sheetName + "]");
            }
            if (StringUtil.isNotEmpty(colDbTypeDDL)) {
                field.setColDbTypeDDL(colDbTypeDDL);
            };

            field.setColBizType(bizType);
            field.setColLength(length);
            field.setColPK(StringUtil.isNotEmpty(primaryKey) ? true : false);
            field.setColNotNull(StringUtil.isNotEmpty(notNull) ? true : false);
            field.setColDefaultValue(defaultValue);
            field.setColFormat(format);
            field.setColMinValue(minValue);
            field.setColMaxValue(maxValue);
            field.setColComment(comment);
            field.setFlagOptimisticCheck(StringUtil.isNotEmpty(optimisticCheck) ? true : false);
            
            if ("自增序列".equals(additionalDefine)) {
                field.setFlagAutoIncrement(true);
            } else if ("逻辑删除".equals(additionalDefine)) {
                field.setFlagLogicalDelete(true);
            } else if ("创建人".equals(additionalDefine)) {
                field.setFlagCreatorId(true);
            } else if ("更新人".equals(additionalDefine)) {
                field.setFlagUpdaterId(true);
            } else if ("创建时间".equals(additionalDefine)) {
                field.setFlagCreateTime(true);
            } else if ("更新时间".equals(additionalDefine)) {
                field.setFlagUpdateTime(true);
            } else if ("版本号".equals(additionalDefine)) {
                field.setFlagUpdateVersion(true);
            }
            bean.getBaseFieldList().add(checkFieldBean(idSet, field));
        }
        
        List<BaseFieldBean> appendFieldList = new ArrayList<>();
        if (bean.isCreateDefaultColumns()) {
            appendFieldList.addAll(createDefaultColumns());
        }
        if (entityConfigurator.getGenericColumnList() != null && !entityConfigurator.getGenericColumnList().isEmpty()) {
            entityConfigurator.getGenericColumnList().forEach(col -> {
                BaseFieldBean genericColumn = new BaseFieldBean();
                BeanUtil.copyProperties(col, genericColumn);
                appendFieldList.add(genericColumn);
            });
        }
        int no = bean.getBaseFieldList().size();
        for (BaseFieldBean field : appendFieldList) {
            field.setColNo(String.valueOf(no++));
            bean.getBaseFieldList().add(checkFieldBean(idSet, field));
        }

        return bean;
    }
    
    /**
     * createDefaultColumns 
     *
     * @return
     */
    protected List<BaseFieldBean> createDefaultColumns() {
        List<BaseFieldBean> list = new ArrayList<>();
        
        BaseFieldBean field = new FieldDescBean();
        field.setColId("CREATOR_ID");
        field.setColName("创建人");
        field.setFlagCreatorId(true);
        field.setColBizType("文本");
        field.setColLength("64");
        list.add(field);
        
        field = new FieldDescBean();
        field.setColId("UPDATER_ID");
        field.setColName("更新人");
        field.setFlagUpdaterId(true);
        field.setColBizType("文本");
        field.setColLength("64");
        list.add(field);

        field = new FieldDescBean();
        field.setColId("CREATE_TIME");
        field.setColName("创建时间");
        field.setFlagCreateTime(true);
        field.setColBizType("时间戳");
        list.add(field);
        
        field = new FieldDescBean();
        field.setColId("UPDATE_TIME");
        field.setColName("更新时间");
        field.setFlagUpdateTime(true);
        field.setColBizType("时间戳");
        list.add(field);
        
        field = new FieldDescBean();
        field.setColId("UPDATE_VERSION");
        field.setColName("版本号");
        field.setColBizType("长整数");
        field.setFlagUpdateVersion(true);
        field.setFlagOptimisticCheck(true);        
        list.add(field);
        
        return list;
    }
    
    /**
     * checkFieldBean 
     *
     * @param idSet
     * @param field
     */
    protected BaseFieldBean checkFieldBean(Set<String> idSet, BaseFieldBean field) {
        if (idSet.contains(field.getColId())) {
            throw new RuntimeException("Found duplicated column ID " + field.getColId());
        }
        idSet.add(field.getColId());
        return field;
    }

    /**
     * isEndRow
     *
     * @param <E>
     * @param row
     * @return
     */
    @Override
    protected <E> boolean isEndRow(List<E> row) {
        if (row.get(0) == null || "null".equalsIgnoreCase(row.get(0).toString()))
            return true;
        else
            return false;
    }

    /**
     * @return the excelTargetColumnLabel
     */
    public String[] getExcelTargetColumnLabel() {
        return excelTargetColumnLabel;
    }

    /**
     * @param excelTargetColumnLabel the excelTargetColumnLabel to set
     */
    public void setExcelTargetColumnLabel(String[] excelTargetColumnLabel) {
        this.excelTargetColumnLabel = excelTargetColumnLabel;
    }

    /**
     * @return the entityGenerator
     */
    public EntityGenerator getEntityGenerator() {
        return entityGenerator;
    }

}
